The invention relates to database processing method and apparatus and, more particularly, to database processing method and apparatus which can define a Large Object data type (LOB type) and an Abstract Data Type (ADT type).
A database management system (hereinbelow, abbreviated to DBMS), particularly, a relational DBMS processes an inquiry expressed by a non-procedure like language. SQL is used as a database language. With respect to the database language SQL, studies regarding the standard of the next SQL (ISO, ANSI) are being progressed at present.
The LOB type is one of main functions of the next SQL. The LOB type is a type which enables the DBMS to handle data such as document, moving picture, audio data, or the like of an amount that is extremely larger than that of data which has hitherto been handled by a conventional database. In accordance with a type of data to be stored, a CLOB type for storing character data such as characters or the like and a BLOB type for storing binary data such as image, audio, or the like are prepared.
As for data such as moving picture, audio, or the like, since a size of each data is large, data of a few megabytes or gigabytes is also considered. It is difficult for the user to store or read out data of the LOB type to/from the DBMS in a lump. Therefore, an interface to divisionally write or read out data by a plurality of operations is generally used.
An example of the operation for such LOB data is shown below. An LOB locator is used in order to hold the LOB data as variables in the DBMS and to operate the LOB data by an UAP (user application). Operation examples are shown by a description by a C language. A sentence structure which begins with "EXEC SQL" buried in the C language program is a description of the DBMS operation.
______________________________________ EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS BLOB.sub.-- LOCATOR resume; char buffer[1000]=" "; int number; int start; int end; EXEC SQL END DECLARE SECTION; ______________________________________
In the above description, variables defined within a range from "SQL BEGIN DECLARE SECTION" to "END DECLARE SECTION" denote a definition of the variables which are used for the DB operation. "resume" denotes the LOB locator.
______________________________________ EXEC SQL DECLARE c1 CURSOR FOR SELECT no , resume FROM emp.sub.-- resume WHERE format=`ascii`; EXEC SQL OPEN cl; EXEC SQL FETCH c1 INTO :number , :resume; ______________________________________
It denotes a description indicating that a retrieval for obtaining values of "no" and "resume" in the line of "format=`ascii`" in an "emp.sub.-- resume" table is executed. It is assumed that the "emp.sub.-- resume" table has previously been defined. First, a cursor "c1" is defined by "DECLARE CURSOR" and the retrieving condition is designated by "SELECT". The cursor "c1" is opened by next "OPEN" and one line adapted to the above condition is extracted by next "FETCH". Now assuming that the "resume" column is of the LOB type, a locator value indicative of the LOB data is set to ":resume".
EXEC SQL SET :buffer=SUBSTR(:resume , :start , :size); PA0 EXEC SQL SET :resume="Hello !!"; PA0 EXEC SQL INSERT into emp.sub.-- resume values(:number, :resume);
It is a description indicating that a SUBSTR function is executed to the LOB data indicated by the ":resume" locator and the result is stored into a "buffer". The SUBSTR function is a process for extracting data within a range from a ":start" position to a ":size" size of the LOB data shown by the ":resume" locator.
It is a description indicating that "Hello!!" is set to the LOB data shown by the ":resume" locator (namely, data is substituted into variables).
It is a description indicating that an integer value of ":number" and the LOB data shown by the ":resume" locator are stored into the "emp.sub.-- resume" table (namely, an insertion of data into an actual database).
As for the LOB data, the operation as mentioned above can be performed.
ADT (Abstract Data Type) is also one of the main functions of the next SQL. ADT is a data type of a user definition in which a type defined by the user can be designated when a table is defined in a manner similar to a type of a system definition. Since the ADT type is a data type formed by the user and is a data type which is not known by the DB, it is difficult to extract the ADT type data by the UAP such as a C language. For this purpose, an operating method using an ADT locator is provided. In a manner similar to the case of the LOB type, an example of the operation for the ADT type data is shown below.
______________________________________ EXEC SQL CREATE VALUE TYPE real.sub.-- estate (rooms INTEGER, size DECIMAL(8,2), text.sub.-- description VARCHAR(1024), location address, document doc); ______________________________________
It is a description indicating that a "real.sub.-- estate" type of the user definition is defined.
______________________________________ EXEC SQL CREATE VALUE TYPE address (street CHAR(30), city CHAR(20), state CHAR(2), zip INTEGER); EXEC SQL CREATE VALUE TYPE doc (format CHAR(20), data BLOB(10M)); ______________________________________
It is a description indicating that an "address" type and a "doc" type are defined in a manner similar to the definition of the "real.sub.-- estate" type.
______________________________________ EXEC SQL CREATE TABLE real.sub.-- estate.sub.-- info (price INTEGER, owner CHAR(40), property real.sub.-- estate); ______________________________________
It is a description indicating that a "real-estate info" table is defined. Particularly, a column of "proper" in the table is set to the "real.sub.-- estate" type of the user definition.
______________________________________ EXEC SQL BEGIN DECLARE SECTION SQL TYPE IS ADT.sub.-- LOCATOR FOR real.sub.-- estate re.sub.-- locator; SQL TYPE IS BLOB(2G) doc.sub.-- file; struct{ short len; char data[1024]; }text.sub.-- descr; EXEC SQL END DECLARE SECTION; ______________________________________
Variables defined within a range from "SQL BEGIN DECLARE SECTION" to "END DECLARE SECTION" in the above description denote a definition of the variables which is used for the DB operation. "re.sub.-- locator" indicates the ADT locator.
______________________________________ EXEC SQL DECLARE rel CURSOR FOR SELECT property FROM real.sub.-- estate.sub.-- info WHERE price&lt;400000 FOR UPDATE OF property; EXEC SQL OPEN rel; ______________________________________
In the above description, a cursor "re1" is defined by "DECLARE CURSOR" and a retrieving condition is designated by "SELECT". A retrieving condition is a retrieval for obtaining "property" in the line of "price&lt;400000" of the "real.sub.-- estate.sub.-- info" table in order to update "property". After that, the cursor "re1" is opened by "OPEN".
______________________________________ while( SQLCODE==0){ EXEC SQL FETCH rel INTQ :re.sub.-- locator; EXEC SQL SET :text.sub.-- descr = text.sub.-- description(:re.sub.-- locator); EXEC SQL SET :doc.sub.-- file = data(document(:re.sub.-- locator)); EXEC SQL UPDATE real.sub.-- estate.sub.-- info SET property = :re.sub.-- locator WHERE CURRENT OF rel; } EXEC SQL CLOSE rel; ______________________________________
The operation which is executed in the above "while" sentence will now be sequentially explained. First, one line is extracted by the cursor "re1" by "FETCH". It denotes that the setting of the current position and the positioning of the ADT locator (a locator value indicative of the ADT data is set to ":re.sub.-- locator") are performed. The next "SET" sentence indicates that the operation to designate ":re-locator" as an ADT locator and to extract "text.sub.-- description" of a variable length character train from "property" as a "real.sub.-- estate" type of the user definition is performed. The next "SET" sentence denotes that the operation to designate ":re.sub.-- locator" as an ADT locator, to extract "document" as a "doc" type from "property" as a "real.sub.-- estate" type of the user definition, to extract BLOB data "data" in the "doc" type data, and to transfer it to a client is executed. The next "UPDATE" indicates the execution of the operation to reflect the value of the ADT data designated by the ADT locator "re.sub.-- locator" to the DB ("real.sub.-- estate.sub.-- info" table). After the "while" sentence, the cursor "re1" is closed by "CLOSE".
The following operation can be executed with respect to the ADT data.
The locator has been disclosed in Don Chamberlin, "Using The New DB2", pp. 236-241, 1996.
On the other hand, in recent years, the application of the relational DBMS has been being progressed mainly with respect to a data process of business affairs, so that a database system which can scalably cope with increases in transaction amount and database amount exceeding a progress of a performance of a CPU or a disk capacity has been strongly required. In response to the request, a parallel DBMS has been proposed by various companies. With respect to a parallel DBMS architecture, for example, there have been proposed three systems as described in FIG. 1 of the article disclosed in "Nikkei Electronics", pages 101 to 114, Feb. 27, 1995. The three systems are a Shared-Everything system in which a main storage and a hard disk drive (HDD) are shared by all processors, a Shared-Disk system in which only the HDD is shared among the processors and the main storage is provided for every processor, and a Shared-Nothing system in which none of the main storage and the HDD is not shared among the processors.
In the parallel DBMS, servers are connected by a network and all of a notification of a control, a movement of data, and the like are performed by communication. Therefore, the movement of data of a large size such as LOB data causes an increase in communication load, so that a load of the whole system is raised.